package jdbc;
import model.Homework;
import model.Student;
import model.Student_homework;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;


public class StudentHomeworkJdbc {

    public static void init(){
        String driverName = "com.mysql.cj.jdbc.Driver";
        try{
            // 加载驱动
            Class.forName(driverName);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List<Student_homework> select_all(){

        String url = "jdbc:mysql://127.0.0.1:3306/test";

        String all_url = url + "?user=root&password=liuyuqi21";

        String sql = "SELECT * FROM student_homework";
        init();
        List<Student_homework> list = new ArrayList<>();
        try(Connection connection = DriverManager.getConnection(all_url)){
            try(Statement statement = connection.createStatement()){
                try(ResultSet resultSet = statement.executeQuery(sql)){
                    while(resultSet.next()){
                        Student_homework sh = new Student_homework();
                        sh.setStudent_id(resultSet.getInt("student_id"));
                        sh.setHomework_id(resultSet.getInt("homework_id"));
                        sh.setHomework_title(resultSet.getString("homework_title"));
                        sh.setHomework_content(resultSet.getString("homework_content"));
                        sh.setTime(resultSet.getTimestamp("time"));
                        list.add(sh);
                        //System.out.println(resultSet.getString(2));
                    }
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static String getPassword(String account){
        String url = "jdbc:mysql://127.0.0.1:3306/test";

        String sql = "SELECT password FROM student where sid=" + account;

        String all_url = url + "?user=root&password=liuyuqi21";

        String password = null;
        init();
        try(Connection connection = DriverManager.getConnection(all_url)){
            try(Statement statement = connection.createStatement()){
                try(ResultSet resultSet = statement.executeQuery(sql)){
                    while (resultSet.next()){
                        password = resultSet.getString("password");
                    }
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return password;
    }

    public static List<Homework> get_student_homework(String account){
        String url = "jdbc:mysql://127.0.0.1:3306/test";

        String all_url = url + "?user=root&password=liuyuqi21";

        String sql = "select * from homework " +
                "where id not in(select homework_id from student_homework where student_id="+ account +");" ;
        init();
        List<Homework> list = new ArrayList<>();
        try(Connection connection = DriverManager.getConnection(all_url)){
            try(Statement statement = connection.createStatement()){
                try(ResultSet resultSet = statement.executeQuery(sql)){
                    while (resultSet.next()){
                        Homework homework = new Homework();
                        homework.setId(resultSet.getInt("id"));
                        homework.setTitle(resultSet.getString("title"));
                        //homework.setContent(resultSet.getString("content"));
                        homework.setTime(resultSet.getTimestamp("create_time"));
                        list.add(homework);
                    }
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void insert_student_homework(String account, String homework_id, String content, String title){
        String url = "jdbc:mysql://127.0.0.1:3306/test";

        String all_url = url + "?user=root&password=liuyuqi21";
        Timestamp ts = new Timestamp(new Date().getTime());
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time_stamp = simpleDateFormat.format(ts);
        System.out.println(time_stamp);
        String sql = "insert into student_homework values(" +
                 account +"," + homework_id + ",'" + title + "','" + content + "','" + time_stamp + "');";
        System.out.println("插入的sql语句： "+sql);
        init();
        try(Connection connection = DriverManager.getConnection(all_url)){
            try(Statement statement = connection.createStatement()){
                int row = statement.executeUpdate(sql);
                System.out.println("insert " + row + "rows insert");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static List<Homework> get_all_homework(){
        String url = "jdbc:mysql://127.0.0.1:3306/test";

        String all_url = url + "?user=root&password=liuyuqi21";

        String sql = "select * from homework " ;
        init();
        List<Homework> list = new ArrayList<>();
        try(Connection connection = DriverManager.getConnection(all_url)){
            try(Statement statement = connection.createStatement()){
                try(ResultSet resultSet = statement.executeQuery(sql)){
                    while (resultSet.next()){
                        Homework homework = new Homework();
                        homework.setId(resultSet.getInt("id"));
                        homework.setTitle(resultSet.getString("title"));
                        //homework.setContent(resultSet.getString("content"));
                        homework.setTime(resultSet.getTimestamp("create_time"));
                        list.add(homework);
                    }
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static List<Student> get_all_student(){
        String url = "jdbc:mysql://127.0.0.1:3306/test";

        String all_url = url + "?user=root&password=liuyuqi21";

        String sql = "select * from student " ;
        init();
        List<Student> list = new ArrayList<>();
        try(Connection connection = DriverManager.getConnection(all_url)){
            try(Statement statement = connection.createStatement()){
                try(ResultSet resultSet = statement.executeQuery(sql)){
                    while (resultSet.next()){
                        Student student = new Student();
                        student.setId(resultSet.getInt("sid"));
                        student.setName(resultSet.getString("sname"));
                        student.setAge(resultSet.getInt("sage"));
                        student.setTel(resultSet.getString("stel"));
                        list.add(student);
                    }
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void add_homework(String title, String homework_id){
        String url = "jdbc:mysql://127.0.0.1:3306/test";

        String all_url = url + "?user=root&password=liuyuqi21";
        Timestamp ts = new Timestamp(new Date().getTime());
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time_stamp = simpleDateFormat.format(ts);
        String sql = "insert into homework values("+homework_id+",'"+ title + "','"+time_stamp+"');";
        System.out.println("sql语句:  "+sql);
        init();
        try(Connection connection = DriverManager.getConnection(all_url)){
            try(Statement statement = connection.createStatement()){
                int row = statement.executeUpdate(sql);
                System.out.println("insert " + row + "rows insert");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void add_student(String id, String name, String age, String tel){
        String url = "jdbc:mysql://127.0.0.1:3306/test";
        String all_url = url + "?user=root&password=liuyuqi21";
        String sql = "insert into student values("+id+",'" + name + "'," + age + ",'" + tel+"','123456');";
        System.out.println("sql3: "+sql);
        init();
        try(Connection connection = DriverManager.getConnection(all_url)){
            try(Statement statement = connection.createStatement()){
                int row = statement.executeUpdate(sql);
                System.out.println("insert " + row + "rows insert");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        List<Homework> list = get_student_homework("1");
        for(int i=0;i<list.size();i++ ){
            Homework homework = list.get(i);
            System.out.print(homework.getTitle());
            System.out.println(homework.getContent());
        }
    }
}
